from dbutils.pooled_db import PooledDB
import pymysql
from flask import current_app, g
from contextlib import contextmanager
import click
import os

# 配置
host = 'localhost'
user = 'jj'
password = '12345678'
database = 'myFlask'
port = 3306

@click.command('init-db')
def init_db_command():
    #初始配置时使用
    db = pymysql.connect(
        host = current_app.config['HOST'],
        user = current_app.config['USER'],
        password = current_app.config['PASSWORD'],
        database = current_app.config['DATABASE'],
        port = current_app.config['PORT']
    )
    cursor = db.cursor()
    
    g.db_cursor = cursor
    g.db_conn = db

    # 获取当前文件(__init__.py)的目录路径
    current_dir = os.path.dirname(os.path.abspath(__file__))
    # 构建schema.sql的绝对路径（在同一个目录）
    schema_path = os.path.join(current_dir, 'schema.sql')

    with open(schema_path, 'r') as f:
        contents = f.read().split(';')
        for content in contents:
            content = content.strip()
            if content:
                cursor.execute(content)
        db.commit()
    click.echo('Initialized the database.')


'''
def release_connection(e=None):
    db_cursor = g.pop('db_cursor', None)
    if 'db_cursor':
        db_cursor.close()
    
    db_conn = g.pop('db_conn', None)
    if 'db_conn':
        db_conn.close()

def init_app(app):
    app.teardown_appcontext (release_connection)
    
    app.cli.add_command(init_db_command)

'''
class mySQLConnectionPool:
    def __init__(self, app=None):
        self.pool = None
        if app:
            self.init_app(app)
    
    def init_app(self, app):
        config = app.config

        self.pool = PooledDB(
            creator = pymysql,
            mincached = 10,
            maxcached = 200,
            blocking = True, # 是否阻塞
            charset='utf8mb4',
            host = config['HOST'],
            user =config['USER'],
            password = config['PASSWORD'],
            database = config['DATABASE'],
            port = config['PORT'],
            cursorclass = pymysql.cursors.DictCursor, # 返回字典格式
            ping = 1
        )

        app.db_pool = self
        app.teardown_appcontext(self.teardown_db)
        
    def get_connection(self):
        ''' 从连接池中获取一个连接 '''
        if self.pool:
            return self.pool.connection()
        else:
            raise RuntimeError('连接池未初始化')

    @staticmethod
    def teardown_db(exception=None):
        """关闭数据库连接和游标"""
        # 关闭游标
        db_cursor = g.pop('db_cursor', None)
        if db_cursor:
            db_cursor.close()
        
        # 归还连接到连接池
        db_conn = g.pop('db_conn', None)
        if db_conn:
            db_conn.close()  # 注意：在PooledDB中这是归还连接
    
    def execute_query(self, conn, sql, cursor, args=None, fetchone=True):
        ''' 执行查询语句，只支持已分配好连接的情况 '''
        if conn.ping(reconnect=True) is None:
            try:
                cursor.execute(sql, args or ())
                conn.commit()
                if fetchone:
                    return cursor.fetchone()
                else:
                    return cursor.fetchall()
            except Exception as e:
                raise e
                

    def execute_update(self, conn, sql, cursor, args=None):
        ''' 执行更新语句，只支持已分配好连接的情况 '''
        if conn.ping(reconnect=True) is None:
            try:
                cursor.execute(sql, args or ())
                conn.commit()
                return cursor.lastrowid
            except pymysql.Error as e:
                conn.rollback()
                raise e
        else:
            print('数据库连接失败！')   

def init_app(app):
    db_pool = mySQLConnectionPool(app)

    @app.before_request
    def before_request():
        if 'db_conn' not in g:
            g.db_conn = db_pool.get_connection()
        if 'db_cursor' not in g:
            g.db_cursor = g.db_conn.cursor()
            # 验证浮标是否有效
    app.cli.add_command(init_db_command)


